<?php

require_once ('system\core\Model.php');

class BookModel extends CI_Model {
	
	function __construct() {
		parent::__construct ();
		$this->load->library('session'); 
		$this->load->database();
	}
	function addreplyinfo($uid,$bookid,$message)
	{
		$sql = "insert into replyinfo values(?,?,?,?,?)"; 
		$this->db->query($sql, array(null,$bookid,$uid,$message,time())); 		
		
		return $this->db->affected_rows();
				
	}
	function addbookinfo($uid,$title,$message)
	{
		$sql = "insert into bookinfo values(?,?,?,?,?,?)"; 
		$this->db->query($sql, array(null,$uid,$title,$message,0,time())); 		
		
		return $this->db->affected_rows();
				
	}
	function updatebookinfo($id,$uid,$title,$message)
	{
		$sql = "update bookinfo set book_title=?,book_message=?,uid=? where bookid=?"; 
		$this->db->query($sql, array($title,$message,$uid,$id)); 
		return $this->db->affected_rows();
				
	}
	function updatebook_view($id){
       $sql = "update bookinfo set view=view+1 where bookid=?"; 
	   $this->db->query($sql, array($id));
	   return $this->db->affected_rows();
	}
	function getbookinfodetail($id)
	{
		$sql="select b.*,r.reply_num,u.name from (bookinfo b left join (select count(*) as reply_num,bookid from replyinfo group by bookid) r on b.bookid=r.bookid) inner join usertable u on b.uid= u.uid where b.bookid=?";
		$query = $this->db->query($sql, array($id));
		return $query->result_array();
	}
	function delete($id)
	{
		$sql="delete c,r from bookinfo c left join replyinfo r on c.bookid=r.bookid where c.bookid=?";
		$query = $this->db->query($sql, array($id));
		return $this->db->affected_rows();
	}
	function delete_reply($id)
	{
		$sql="delete from replyinfo where rid=?";
		$query = $this->db->query($sql, array($id));
		return $this->db->affected_rows();
	}
	function getbookinfo()
	{
		return $this->db->count_all('bookinfo');  
	}
	function getbooklist($start,$size)
	{
		$sql="select b.*,r.reply_num,u.name from (bookinfo b left join (select count(*) as reply_num,bookid from replyinfo group by bookid) r on b.bookid=r.bookid) inner join usertable u on b.uid= u.uid limit ?,?";
		$query = $this->db->query($sql, array($start,$size));
		return $query;
	}
	function getreplylist($start,$size,$id)
	{
		$sql="select r.*,u.name from replyinfo r  inner join usertable u on r.uid= u.uid  where r.bookid=".$id." limit ?,?";
		$query = $this->db->query($sql, array($start,$size));
		return $query;
	}
	
	function getcaseinfoByCond($case_name
	,$case_type
	,$case_status
	,$incident_time_begin
	,$incident_time_end
	,$court_date_begin
	,$court_date_end
	,$prosecutor
	,$defendant)
	{
		
		$sql="select count(*) as cnt from caseinfo where case_status = ? "
		 ." and case_type=? and case_name like ? "
		 ." and defendant like ? and prosecutor like ? "
		 ." and (court_date >= ? or '' = ? ) "
		 ." and (court_date <= ? or '' = ? ) "
		 ." and (incident_time >= ? or '' = ? ) "
		 ." and (incident_time <= ? or '' = ? ) ";
		$query = $this->db->query($sql, array($case_status
			,$case_type
			,"%".$case_name."%"
			,"%".$defendant."%"
			,"%".$prosecutor."%"
			,$court_date_begin
			,$court_date_begin
			,$court_date_end
			,$court_date_end
			,$incident_time_begin
			,$incident_time_begin
			,$incident_time_end
			,$incident_time_end));
		 $array= $query->result_array();
		return $array[0]["cnt"];
	}
	function getcaselistByCond($start,$size,$case_name
		,$case_type
		,$case_status
		,$incident_time_begin
		,$incident_time_end
		,$court_date_begin
		,$court_date_end
		,$prosecutor
		,$defendant)
	{
		$sql="select * from caseinfo where case_status = ? "
		 ." and case_type=? and case_name like ? "
		 ." and defendant like ? and prosecutor like ? "
		 ." and (court_date >= ? or '' = ? ) "
		 ." and (court_date <= ? or '' = ? ) "
		 ." and (incident_time >= ? or '' = ? ) "
		 ." and (incident_time <= ? or '' = ? ) "
		 ." limit ?,?";
		$query = $this->db->query($sql, array($case_status
			,$case_type
			,"%".$case_name."%"
			,"%".$defendant."%"
			,"%".$prosecutor."%"
			,$court_date_begin
			,$court_date_begin
			,$court_date_end
			,$court_date_end
			,$incident_time_begin
			,$incident_time_begin
			,$incident_time_end
			,$incident_time_end
			,$start
			,$size
			));
		return $query;
		
	}
}

?>